using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;

namespace Schemata.Core
{
    /// <summary>
    /// Represents a schema provider for SQL Server.
    /// </summary>
    /// <remarks>
    /// Found this article that explains how to use GetOleDbSchemaTable:
    /// http://support.microsoft.com/?id=309488
    /// Found this site that has a mapping of SQL Server data types to
    /// .NET types:
    /// http://media.datadirect.com/download/docs/dotnet/dotnetref/dotnetss.html#wp928843
    /// </remarks>
    public class SqlServerSchemaProvider : SchemaProvider
    {
        /// <summary>
        /// Returns a list of FKs for the given table.
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        /// <remarks>
        /// Found this article that describes Foreign_Keys:
        /// http://weblogs.asp.net/rosherove/archive/2003/07/01/9542.aspx
        /// </remarks>
        public override List<TableKeySchema> GetForeignKeys(TableSchema table)
        {
            List<TableKeySchema> list = new List<TableKeySchema>();

            OleDbConnection conn = new OleDbConnection(table.Database.ConnectionString);
            conn.Open();
            object[] restrictions = new object[] { null, null, table.Name, null };
            DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, restrictions);
            conn.Close();

            foreach (DataRow row in schemaTable.Rows)
            {
                TableKeySchema fk = new TableKeySchema();

                // GET FK NAME.
                fk.Name = row["FK_NAME"].ToString();

                // GET COLUMN THAT IS PK OF THIS TABLE.
                string pkColumnName = row["PK_COLUMN_NAME"].ToString();
                fk.ForeignKeyMemberColumns.Add(table.GetColumnSchemaByName(pkColumnName));

                // GET COLUMN THAT IS THE FK OF THE OTHER TABLE.
                string fkColumnName = row["FK_COLUMN_NAME"].ToString();
                string fkTableName = row["FK_TABLE_NAME"].ToString();

                // FIND THE OTHER TABLE.
                TableSchema otherTable = table.Database.GetTableSchemaByName(fkTableName);
                fk.ForeignKeyMemberColumns.Add(otherTable.GetColumnSchemaByName(fkColumnName));

                // SETUP THE PRIMARY KEY TABLE.
                fk.PrimaryKeyTable = table;

                list.Add(fk);
            }
                        
            return list;
        }
        
                
        /// <summary>
        /// Returns a value indicating if the given columnRow is a primary key for the
        /// given table.
        /// </summary>
        /// <param name="table"></param>
        /// <param name="columnRow"></param>
        /// <returns></returns>
        /// <remarks>
        /// Found this article that explains the Primary_Keys call syntax:
        /// http://www.kbalertz.com/309681/Retrieve.Schema.Information.Using.GetOleDbSchemaTable.Visual.aspx
        /// </remarks>
        public override bool GetIsPrimaryKeyMember(TableSchema table , DataRow columnRow)
        {
            bool result = false;

            OleDbConnection conn = new OleDbConnection(table.Database.ConnectionString);
            conn.Open();
            object[] restrictions = new object[] { table.Database.Name , "dbo", table.Name };
            DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, restrictions);
            conn.Close();
            conn = null;

            string columnName = columnRow["COLUMN_NAME"].ToString();

            foreach (DataRow row in schemaTable.Rows)
            {
                string pkColumnName = row["COLUMN_NAME"].ToString();

                if (pkColumnName == columnName)
                {
                    result = true;
                    break;
                }
            }

            return result;
        }

        /// <summary>
        /// Returns the DbType of the given DataRow containing Column information.
        /// </summary>
        /// <param name="columnRow"></param>
        /// <returns></returns>
        /// <remarks>
        /// Created a table called EverythingTable in a sample database and 
        /// ran exec sp_columns_rowset N'EverythingTable', NULL, NULL.  This is the
        /// underlying DB call that the OldDbConnection executes.  Saved the resulting
        /// data table to an XLS file: SqlServerColumnInformation.xls
        /// Had to do an mapping of the resulting DATA_TYPE code to the value
        /// in the DbType enum.
        /// </remarks>
        public override DbType GetDataType(DataRow columnRow)
        {
            string dataType = columnRow["DATA_TYPE"].ToString().Trim();

            DbType result;

            switch (dataType)
            {
                case ("2"): result = DbType.Int16; break; //SmallIntColumn
                case ("3"): result = DbType.Int32; break; //IntColumn
                case ("4"): result = DbType.Single; break;//RealColumn
                case ("5"): result = DbType.Double; break;//FloatColumn
                case ("6"): result = DbType.Decimal; break;//SmallMoneyColumn+MoneyColumn
                case ("11"): result = DbType.Boolean; break;//BitColumn
                case ("12"): throw new ApplicationException("Don't know what to do with SqlVariant");//SqlVariantColumn
                case ("17"): result = DbType.Byte; break;//TinyIntColumn
                case ("20"): result = DbType.Int64; break;//BigIntColumn
                case ("72"): result = DbType.Guid; break;//UniqueIdentifierColumn
                case ("128"): result = DbType.Binary; break;//VarBinaryColumn+TimeStampColumn+ImageColumn+BinaryColumn
                case ("129"): result = DbType.String; break;//VarCharColumn+TextColumn+CharColumn
                case ("130"): result = DbType.String; break;//NVarCharColumn+NTextColumn+NCharColumn
                case ("131"): result = DbType.Decimal; break;//NumericColumn+DecimalColumn
                case ("135"): result = DbType.DateTime; break;//SmallDateTimeColumn+DateTimeColumn
                default: throw new ApplicationException("Can't translate dataType:"+dataType);       
            }
            return result;
        }

        /// <summary>
        /// Represents a raw table column.
        /// </summary>
        public class RawColumn
        {
            /// <summary>
            /// The column name.
            /// </summary>
            public string ColumnName;

            /// <summary>
            /// The data type.
            /// </summary>
            public string DataType;
        }

        /// <summary>
        /// Gets the columns.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <returns></returns>
        public override List<ColumnSchema> GetColumns(TableSchema table)
        {
            OleDbConnection conn = new OleDbConnection(table.Database.ConnectionString);
            conn.Open();
            object[] restrictions = new object[] { null, null, table.Name , null };
            DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, restrictions);
            conn.Close();

            List<ColumnSchema> list = new List<ColumnSchema>();
            foreach (DataRow row in schemaTable.Rows)
            {
                string columnName = row["COLUMN_NAME"].ToString();
                bool isPrimaryKeyMember = GetIsPrimaryKeyMember(table,row);
                DbType dataType = GetDataType(row);
                ColumnSchema obj = new ColumnSchema(columnName,table,isPrimaryKeyMember,dataType);
                list.Add(obj);
            }

            return list;
        }

        /// <summary>
        /// Gets the tables.
        /// </summary>
        /// <param name="database">The database.</param>
        /// <returns></returns>
        public override List<TableSchema> GetTables(DatabaseSchema database)
        {
            OleDbConnection conn = new OleDbConnection(database.ConnectionString);
            conn.Open();
            object[] restrictions = new object[] { null, null, null, "TABLE" };
            DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, restrictions);
            conn.Close();

            List<TableSchema> list = new List<TableSchema>();
            foreach (DataRow row in schemaTable.Rows)
            {
                TableSchema obj = new TableSchema();
                obj.Name = row["TABLE_NAME"].ToString();
                obj.Database = database;
                list.Add(obj);
            }

            return list;            
        }
    }
}
